<?php   if (!defined('BASEPATH')) exit('No direct script access allowed');


class Bacini_model extends CI_Model {
	
	private $params = array();
    function __construct()
    {
        parent::__construct();
		
    }

    function get_columns()
	{
		$sql = "SELECT column_name from information_schema.columns where
table_name='" . $this->db->dbprefix('bacini') . "'";
		$query = $this->db->query($sql);
		
		return $query->result_array();
	}
    
	function get_total($params = array())
	{
		
		if(empty($this->params))
		{
			$default_params = array
			(
				'where' => null,
				'group_by' => array()
			);
			
			foreach ($default_params as $key => $value)
			{
				$this->params[$key] = (!empty($params[$key]))? $params[$key]: $default_params[$key];
			}
		}
        
		
		$this->db->select('b.id');
		$this->db->from('bacini as b');
		$this->db->where( $this->params['where']);
        //var_dump($this->params);
        $query = $this->db->get();
		$nb = $query->num_rows();
		return $nb;
	}
		

	
	function get_list($params = array())
	{
        
		$default_params = array
		(
			'select' => 'b.*',
			'where' => null,
			'limit' => null,
			'start' => 0,
			'order_by' => 'b.id DESC'
		);
        
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}
        $this->params = $params;
		$this->db->select($params['select']);
		$this->db->from('bacini as b');
		if(!is_null($params['where']))
		{
			$this->db->where($params['where']);
		}
		if(!is_null($params['limit']))
		{
			$this->db->limit($params['limit'], $params['start']);
		}
		$this->db->order_by($params['order_by']);
		
		
        $query = $this->db->get();
        
		
		return $query->result_array();
	}


	function get_list_2($params = array())
	{
        
		$default_params = array
		(
			'where' => null,
		);
        
        
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}
        $sql = 'SELECT bacino, cmp, prov, dest_tarif, sum(cnt) as total FROM (SELECT SUBSTRING("b"."cap_start" FROM 1 FOR 3) AS ind, "b"."bacino", "b"."cmp", "b"."prov", "b"."dest_tarif", count(s.id) as cnt FROM "bacini" as b LEFT JOIN "sostenitori" as s ON "s"."cap" >= "b"."cap_start" AND s.cap <= b.cap_end WHERE ' . $params['where'] . ' GROUP BY "b"."bacino", "b"."cmp", "b"."prov", "b"."dest_tarif", "b"."cap_start", "b"."cap_end" ORDER BY "b"."cap_start") as foo group by ind, bacino, cmp, prov, dest_tarif ORDER BY ind';
		
        $query = $this->db->query($sql);
        
		
		return $query->result_array();
	}

	function get_list_4($params = array())
	{
        
		$default_params = array
		(
			'select' => "b.bacino, b.prov, b.destinazione, b.misc, b.dest_tarif, b.cap, b.ordine, UPPER(s.citta) AS citta, count(s.id) as cnt",
			'where' => null,
			'group_by' => "b.bacino, b.prov, b.destinazione, b.misc, b.dest_tarif, b.ordine, b.cap, UPPER(s.citta)",
			'order_by' => "b.ordine, b.cap",
//			'having' => "count (s.id) >= 10"
			'having' => null
		);
         
        
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}

		$sql = "SELECT " . $params['select'] . " FROM cap_bacini as b JOIN sostenitori as s ON s.cap = b.cap WHERE " . $params['where'] . " GROUP BY " . $params['group_by'] . (is_null($params['having'])? "": " HAVING " . $params['having']) . " ORDER BY " . $params['order_by'];
		
        $query = $this->db->query($sql);
        
		
		return $query->result_array();
	}

	function get_list_5($params = array())
	{
        
		$default_params = array
		(
			'select' => "b.bacino, b.gruppo, b.prov, b.destinazione, b.misc, b.dest_tarif, b.cap, b.ordine, UPPER(s.citta) AS citta",
			'where' => null,
			'order_by' => "b.ordine, b.cap",
			'having' => null
		);
         
        
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}

		$this->db->select($params['select']);
		$this->db->from('cap_bacini as b');
		$this->db->join('sostenitori as s', 's.cap=b.cap', 'left');
		$this->db->join('titoli as t', 't.id = s.id_titolo', 'left');
		if(!is_null($params['where'])) $this->db->where($params['where']);
		
        $query = $this->db->get();
        
		
		return $query->result_array();
	}

	
    function get_sostenitori($params = array())
	{
        
		$default_params = array
		(
			'where' => null
		);
        
        
		foreach ($default_params as $key => $value)
		{
			$params[$key] = (isset($params[$key]))? $params[$key]: $default_params[$key];
		}
        
        $sql = "SELECT s.*, t.nome as titolo FROM sostenitori as s LEFT JOIN cap_bacini as b ON s.cap = b.cap LEFT JOIN titoli as t ON t.id = s.id_titolo "; 
 		if(!is_null($params['where']))
		{
			$sql .= " WHERE " . $params['where'];
		}
        $sql .= " ORDER BY s.cap";
		
        $query = $this->db->query($sql);
       
		
		return $query->result_array();
	}
}